create proc [dbo].[p_WaitState10PointTrends](
			@DatabaseID bigint, 
			@StartDate datetime,
			@EndDate datetime, 
			@WaitState varchar(50)=null) as 

set nocount on

declare @RangeHours int
set @RangeHours=datediff(hour,@StartDate,@EndDate)/10

declare @rangedates table([RangeStart] datetime,[RangeEnd] datetime)

declare @CurRange int
set @CurRange=0
while @CurRange<10
begin
	insert into @rangedates([RangeStart],[RangeEnd]) 
	select
		dateadd(hour,@RangeHours*@CurRange,@StartDate),
		dateadd(hour,@RangeHours*(@CurRange+1),@StartDate)

	set @CurRange=@CurRange+1
end

create table #y (
	Wait_Type varchar(50) not null,
	P1_AvgRequests decimal(18,3) null,
	P2_AvgRequests decimal(18,3) null,
	P3_AvgRequests decimal(18,3) null,
	P4_AvgRequests decimal(18,3) null,
	P5_AvgRequests decimal(18,3) null,
	P6_AvgRequests decimal(18,3) null,
	P7_AvgRequests decimal(18,3) null,
	P8_AvgRequests decimal(18,3) null,
	P9_AvgRequests decimal(18,3) null,
	P10_AvgRequests decimal(18,3) null,
	P1_AvgWaitTime decimal(18,3) null,
	P2_AvgWaitTime decimal(18,3) null,
	P3_AvgWaitTime decimal(18,3) null,
	P4_AvgWaitTime decimal(18,3) null,
	P5_AvgWaitTime decimal(18,3) null,
	P6_AvgWaitTime decimal(18,3) null,
	P7_AvgWaitTime decimal(18,3) null,
	P8_AvgWaitTime decimal(18,3) null,
	P9_AvgWaitTime decimal(18,3) null,
	P10_AvgWaitTime decimal(18,3) null,	
	P1_AvgSignalTime decimal(18,3) null,
	P2_AvgSignalTime decimal(18,3) null,
	P3_AvgSignalTime decimal(18,3) null,
	P4_AvgSignalTime decimal(18,3) null,
	P5_AvgSignalTime decimal(18,3) null,
	P6_AvgSignalTime decimal(18,3) null,
	P7_AvgSignalTime decimal(18,3) null,
	P8_AvgSignalTime decimal(18,3) null,
	P9_AvgSignalTime decimal(18,3) null,
	P10_AvgSignalTime decimal(18,3) null)

select distinct 
	a.[SessionID], 
	a.[Wait_Type],
	a.[Requests],
	a.[Wait_Time],
	a.[Signal_Wait_Time],
	a.[CreateDate]
into
	#w
from
	vWaitStateHistory a
where
	a.[DatabaseID]=@DatabaseID
	and a.[CreateDate] between @StartDate and @EndDate

if @WaitState is null
	insert into #y (
		[Wait_Type])
	select distinct
		a.[Wait_Type]   
	from
		#w a
else
	insert into #y (
		[Wait_Type])
	select distinct
		a.[Wait_Type]   
	from
		#w a
	where
		a.[Wait_Type]=@WaitState

declare @RangeStart datetime,
	@RangeEnd datetime

declare @RangeData table(
			[wait_type] varchar(50),
			[AvgWaitTime] decimal(18,3),
			[AvgSignalWaitTime] decimal(18,3),
			[AvgRequests] decimal(18,3))
			
set @CurRange=0

declare xcn cursor local forward_only for
select
	[RangeStart],
	[RangeEnd]
from
	@rangedates

open xcn

fetch next from xcn into @RangeStart,@RangeEnd

while @@fetch_status=0
begin
	delete from @RangeData

	insert into @RangeData(
		[wait_type],
		[AvgWaitTime],
		[AvgSignalWaitTime],
		[AvgRequests]
	)
	select
		a.[Wait_Type],
		sum(a.[Wait_Time])/count(distinct a.[SessionID]) 'AvgWaitTime',
		sum(a.[Signal_Wait_Time])/count(distinct a.[SessionID]) 'AvgSignalWaitTime',
		sum(a.[Requests])/count(distinct a.[SessionID]) 'AvgRequests'
	from
		#w a
	where
		a.[CreateDate] between @RangeStart and @RangeEnd
	group by
		a.[Wait_Type]

	if @CurRange=0
		update #y set 
			P1_AvgRequests=a.[AvgRequests],
			P1_AvgWaitTime=a.[AvgWaitTime],
			P1_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]

	else if @CurRange=1
		update #y set 
			P2_AvgRequests=a.[AvgRequests],
			P2_AvgWaitTime=a.[AvgWaitTime],
			P2_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=2
		update #y set 
			P3_AvgRequests=a.[AvgRequests],
			P3_AvgWaitTime=a.[AvgWaitTime],
			P3_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=3
		update #y set 
			P4_AvgRequests=a.[AvgRequests],
			P4_AvgWaitTime=a.[AvgWaitTime],
			P4_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=4
		update #y set 
			P5_AvgRequests=a.[AvgRequests],
			P5_AvgWaitTime=a.[AvgWaitTime],
			P5_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=5
		update #y set 
			P6_AvgRequests=a.[AvgRequests],
			P6_AvgWaitTime=a.[AvgWaitTime],
			P6_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]

	else if @CurRange=6
		update #y set 
			P7_AvgRequests=a.[AvgRequests],
			P7_AvgWaitTime=a.[AvgWaitTime],
			P7_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=7
		update #y set 
			P8_AvgRequests=a.[AvgRequests],
			P8_AvgWaitTime=a.[AvgWaitTime],
			P8_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=8
		update #y set 
			P9_AvgRequests=a.[AvgRequests],
			P9_AvgWaitTime=a.[AvgWaitTime],
			P9_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]


	else if @CurRange=9
		update #y set 
			P10_AvgRequests=a.[AvgRequests],
			P10_AvgWaitTime=a.[AvgWaitTime],
			P10_AvgSignalTime=a.[AvgSignalWaitTime]
		from
			@RangeData a
		where
			a.[Wait_Type]=#y.[Wait_Type]

	
		
	set @CurRange=@CurRange+1

	fetch next from xcn into @RangeStart,@RangeEnd
end
close xcn
deallocate xcn

select 
	a.[Wait_Type],
	b.[Desc],
	a.[P1_AvgRequests],    
	a.[P1_AvgWaitTime],    
   	a.[P1_AvgSignalTime],  

	a.[P2_AvgRequests],    
	a.[P2_AvgWaitTime], 
    	a.[P2_AvgSignalTime], 

	a.[P3_AvgRequests],  
	a.[P3_AvgWaitTime], 
      	a.[P3_AvgSignalTime], 

	a.[P4_AvgRequests], 
	a.[P4_AvgWaitTime], 
       	a.[P4_AvgSignalTime], 

	a.[P5_AvgRequests], 
	a.[P5_AvgWaitTime], 
      	a.[P5_AvgSignalTime], 

	a.[P6_AvgRequests], 
       	a.[P6_AvgWaitTime], 
	a.[P6_AvgSignalTime], 

	a.[P7_AvgRequests], 
       	a.[P7_AvgWaitTime], 
	a.[P7_AvgSignalTime], 

	a.[P8_AvgRequests],  
	a.[P8_AvgWaitTime], 
      	a.[P8_AvgSignalTime], 

	a.[P9_AvgRequests],    
	a.[P9_AvgWaitTime], 
    	a.[P9_AvgSignalTime],  

	a.[P10_AvgRequests],            
	a.[P10_AvgWaitTime],         
	a.[P10_AvgSignalTime]    
from 
	#y a
		left outer join [WaitStates] b on b.[Name]=a.[Wait_Type]
order by
	a.[Wait_Type]

drop table #y
drop table #w


